package com.old.nsi_class.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.old.nsi_class.model.*;
/**
 * 增删改查方法
 * @author 12984
 *
 */
public class Course_DB {

	/**
	 * 增/删/改通用方法
	 * @param sql 数据库语句
	 * @return 返回数据结果集
	 */
	public static int commonMethod(String sql){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int rows = 0;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rows = ps.executeUpdate();
			if(rows>0) {
				//System.out.println("插入/修改/删除成功...");
			}else {
				System.out.println("插入/修改/删除失败...");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//释放资源
			DBUtils.getClose(conn, ps, rs);
		}
		return rows;
	}
	
	/**
	 * 图片系统查询
	 * @param sql
	 * @return
	 */
	public static List<Image_model> searchImage(String sql){
		List<Image_model> list = new ArrayList<Image_model>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Image_model im = new Image_model();
				receiver(rs, im);
				list.add(im);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
	}
	
	
	/**
	 * 激活码查询
	 * @param sql
	 * @return <Teacher_model>的list
	 */
	public static List<ActivationCode> searchCode(String sql){
		List<ActivationCode> list = new ArrayList<ActivationCode>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				ActivationCode ac = new ActivationCode();
				receiver(rs, ac);
				list.add(ac);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
	}
	
	/**
	 * 教师表查询
	 * @param sql
	 * @return <Teacher_model>的list
	 */
	public static List<Teacher_model> searchTeacher(String sql){
		List<Teacher_model> list = new ArrayList<Teacher_model>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Teacher_model tm = new Teacher_model();
				receiver(rs, tm);
				list.add(tm);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
	}
	
	/**
	 * 查询方法
	 * @param sql
	 * @return <Course_model>的list
	 */
	public static List<Teacher_model> teacherSearch(String sql){
		List<Teacher_model> list = new ArrayList<Teacher_model>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int row = ps.executeUpdate();
			rs = ps.executeQuery();
			if(row>0) {
				System.out.println("courseSearch方法插入成功,影响行数:"+row);
			}else {
				System.out.println("courseSearch方法插入失败,影响行数:"+row);
			}
			conn.close();
		} catch (Exception e) { 
			System.out.println("teacherSearch方法异常...");
			e.printStackTrace();
		}finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
		
	}
	
	/**
	 * 查询方法
	 * @param sql
	 * @return <Activity_model>的list
	 */
	public static List<Activity_model> searchActivity(String sql){
		List<Activity_model> list = new ArrayList<Activity_model>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Activity_model as = new Activity_model();
				receiver(rs, as);
				list.add(as);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
	}
	
	/**
	 * 计数方法返回多少条
	 * @param sql
	 * @return
	 */
	public static int count(String sql){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int countNum=-1;
		try{	
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.last();
			countNum=rs.getRow();
		}
		catch(Exception e){
			e.printStackTrace();
		} finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return countNum;
	}
	

		
	
//	---------------------------------课程相关---------------------------------------

	
	
    //插入课程用户关联表，返回list
	public static List<Course_User_model> insert01(String sql){
		List<Course_User_model> list = new ArrayList<Course_User_model>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int row = ps.executeUpdate();
			if(row>0) {
				//System.out.println("insert方法插入成功,影响行数:"+row);
			}else {
				System.out.println("insert方法插入失败,影响行数:"+row);
			}
			conn.close();
		} catch (Exception e) {
			System.out.println("insert 插入Sql方法异常...");
			e.printStackTrace();
		}finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
	}
	
//	插入课程用户关联表，返回int,影响行数
	public static int insert02(String sql){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int row = 0;
		try {
			conn = DBUtils.getInstance().getConnection();	
			ps = conn.prepareStatement(sql);				
			row = ps.executeUpdate();
			if(row>0) {
				//System.out.println("insert方法插入成功,影响行数:"+row);
			}else {
				System.out.println("insert方法插入失败,影响行数:"+row);
			}
			conn.close();
		} catch (Exception e) {
			System.out.println("insert 插入Sql方法异常...");
			e.printStackTrace();
		}finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return row;
	}
	
	
	
	/**
	 * 课程用户查询
	 * @param sql
	 * @return
	 */
//	public static List<Course_User_model> courseUserSearch(String sql){
//		List<Course_User_model> list = new ArrayList<Course_User_model>();
//		Connection conn = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		try {
//			conn = DBUtils.getInstance().getConnection();
//			ps = conn.prepareStatement(sql);
//			int row = ps.executeUpdate();
//			if(row>0) {
//				System.out.println("courseSearch方法插入成功,影响行数:"+row);
//			}else {
//				System.out.println("courseSearch方法插入失败,影响行数:"+row);
//			}
//			conn.close();
//		} catch (Exception e) { 
//			System.out.println("courseUserSearch方法异常...");
//			e.printStackTrace();
//		}finally {
//			DBUtils.getClose(conn, ps, rs);
//		}
//		return list;
//	}
	
		
//	搜索 返回list
	public static List<Course_model> CourseSearch(String sql)
	{	
		List<Course_model> list = new ArrayList<Course_model>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		try {

			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();		
			while(rs.next()) {
				Course_model model = new Course_model();
				receiver(rs, model);
				list.add(model);
			}		
		} catch (Exception e) {
			System.out.println("select 搜索Sql方法异常...");
			e.printStackTrace();
		}finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
	}
	
//	课程删除
	public static int CourseDelete(String sql)
	{
		int row=-2;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{	
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			row = ps.executeUpdate();
			System.out.println("Class_DB.java:删除：删除动作已执行");	
		}
		catch(Exception e)
		{
			System.out.println("Class_DB.java:删除sql异常");	
			e.printStackTrace();
		}finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return row;
	}
	
	
	/**
	 * 课程购买码-查询方法
	 * @param sql
	 * @return <Course_model>的list
	 */
	public static List<ActivationCode> ActivationCodeSearch(String sql){
		List<ActivationCode> list = new ArrayList<ActivationCode>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();		
			while(rs.next()) {
				ActivationCode model = new ActivationCode();
				receiver(rs, model);
				list.add(model);
			}		
		} catch (Exception e) {
			System.out.println("ActivationCodeSearch:Sql方法异常...");
			e.printStackTrace();
		}finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
		
	}
	
	
//	搜索 课程用户购买表
	public static List<Course_User_model> CourseUserSearch(String sql)
	{	
		List<Course_User_model> list = new ArrayList<Course_User_model>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		try {
			conn = DBUtils.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();		
			while(rs.next()) {
				Course_User_model model = new Course_User_model();
				receiver(rs, model);
				list.add(model);
			}		
		} catch (Exception e) {
			System.out.println("CourseUserSearch 搜索Sql方法异常...");
			e.printStackTrace();
		}finally {
			DBUtils.getClose(conn, ps, rs);
		}
		return list;
	}
	
//	---------------------------------通用返回值参数---------------------------------------
	
	//封装教师参数
	private static void receiver(ResultSet rs, Image_model si) throws SQLException {
		si.setId(rs.getInt("id"));
		si.setLowFile(rs.getString("lowFile"));
		si.setBigFile(rs.getString("bigFile"));
		si.setCreateTime(rs.getString("create_time"));

	}
	
	//封装教师参数
	private static void receiver(ResultSet rs, Teacher_model tm) throws SQLException {
		tm.setId(rs.getInt("Id"));
		tm.setTeacherName(rs.getString("TeacherName"));
		tm.setTeacherDescription(rs.getString("TeacherDescription"));
		tm.setTeacherCourse(rs.getString("teacherCourse"));
		tm.setTeacherImage(rs.getString("TeacherImage"));
		tm.setHtml01(rs.getString("Html01"));
		tm.setLoad_people(rs.getString("Load_people"));
		tm.setLoad_time(rs.getString("Load_time"));
	}
	
	//封装课程参数
	private static void receiver(ResultSet rs, Course_model model) throws SQLException {
		model.setId(rs.getInt("Id"));
		model.setChannelNumber(rs.getString("ChannelNumber"));
		model.setChannelName(rs.getString("ChannelName"));
		model.setSecretkey(rs.getString("Secretkey"));
		model.setTeacherId(rs.getString("TeacherId"));
		model.setHtml01(rs.getString("Html01"));
		model.setHtml02(rs.getString("Html02"));
		model.setHtml03(rs.getString("Html03"));
		model.setHtml04(rs.getString("Html04"));
		model.setCourseSubject(rs.getString("CourseSubject"));
		model.setCourseName(rs.getString("CourseName"));
		model.setCourseState(rs.getString("CourseState"));
		model.setCourseDescription(rs.getString("CourseDescription"));
		model.setClassBegins(rs.getString("ClassBegins"));
		model.setCoursePraise(rs.getString("CoursePraise"));
		model.setCoursePrice(rs.getString("CoursePrice"));
		model.setCourseRelease(rs.getString("CourseRelease"));
		model.setCoursePeople(rs.getString("CoursePeople"));
		model.setCoverImage(rs.getString("CoverImage"));
		model.setCourseImage(rs.getString("CourseImage"));
	}
	
	//封装活动参数
	private static void receiver(ResultSet rs, Activity_model as) throws SQLException {
		as.setId(rs.getInt("Id"));
		as.setTitle1(rs.getString("Title1"));
		as.setContent1(rs.getString("Content1"));
		as.setTitle2(rs.getString("Title2"));
		as.setContent2(rs.getString("Content2"));
		as.setTitle3(rs.getString("Title3"));
		as.setContent3(rs.getString("Content3"));
		as.setTitle4(rs.getString("Title4"));
		as.setContent4(rs.getString("Content4"));
		as.setTitle5(rs.getString("Title5"));
		as.setContent5(rs.getString("Content5"));
		as.setTitle6(rs.getString("Title6"));
		as.setContent6(rs.getString("Content6"));
		as.setTitle7(rs.getString("Title7"));
		as.setContent7(rs.getString("Content7"));
		as.setTitle8(rs.getString("Title8"));
		as.setContent8(rs.getString("Content8"));
		as.setTitle9(rs.getString("Title9"));
		as.setContent9(rs.getString("Content9"));
		as.setTitle10(rs.getString("Title10"));
		as.setContent10(rs.getString("Content10"));
		as.setTitle11(rs.getString("Title11"));
		as.setContent11(rs.getString("Content11"));
		as.setTitle12(rs.getString("Title12"));
		as.setContent12(rs.getString("Content12"));
		as.setTitle13(rs.getString("Title13"));
		as.setContent13(rs.getString("Content13"));
		as.setTitle14(rs.getString("Title14"));
		as.setContent14(rs.getString("Content14"));
		as.setTitle15(rs.getString("Title15"));
		as.setContent15(rs.getString("Content15"));
		as.setTitile16(rs.getString("Title16"));
		as.setContent16(rs.getString("Content16"));
		as.setTitle17(rs.getString("Title17"));
		as.setContent17(rs.getString("Content17"));
		as.setTitle18(rs.getString("Title18"));
		as.setContent18(rs.getString("Content18"));
		as.setTitle19(rs.getString("Title19"));
		as.setContent19(rs.getString("Content19"));
		as.setTitle20(rs.getString("Title20"));
		as.setContent20(rs.getString("Content20"));
		as.setDeadline(rs.getString("Deadline"));
		as.setLoad_time(rs.getString("Load_time"));
	}
	
	//封装课程购买码参数
	private static void receiver(ResultSet rs, ActivationCode model) throws SQLException {
		model.setId(rs.getInt("Id"));
		model.setActivationCode(rs.getString("activationCode"));
		model.setLoadTime(rs.getString("loadTime"));
		model.setUserTime(rs.getString("userTime"));
		model.setValid(rs.getString("valid"));
		model.setText(rs.getString("text"));
	}
	
	//封装课程用户购买表
	private static void receiver(ResultSet rs,Course_User_model model) throws SQLException {
		model.setId(rs.getInt("Id"));
		model.setClassId(rs.getString("ClassId"));
		model.setUserMail(rs.getString("UserMail"));
	}
	
}
